﻿using QuanLyGiaoVuDAL.ADOWrapper;
using QuanLyGiaoVuDAL.DTO;
using QuanLyGiaoVuDAL.Mapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace QuanLyGiaoVuDAL.Repositories.DatabaseRepositories
{
    public class HocKyRepository : IHocKyRepository
    {
        public DTO.HocKyDTO GetHocKyById(Guid id)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_HocKySelect]", new SqlParameter("@MaHocKy", id));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToHocKyDTO();
        }

        public IEnumerable<DTO.HocKyDTO> GetAllHocKy()
        {
            List<HocKyDTO> hocKyList = new List<HocKyDTO>();
            var parameter = DBParameterFactory.Create("MaHocKy", SqlDbType.UniqueIdentifier, DBNull.Value);
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_HocKySelect]", parameter);
            var giangvienTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in giangvienTable)
            {
                hocKyList.Add(dataRow.ToHocKyDTO());
            }
            return hocKyList.AsEnumerable();
        }

        public DTO.HocKyDTO InsertHocKy(DTO.HocKyDTO hocKyDTO)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_HocKyInsert]", GetParameterArray(hocKyDTO));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToHocKyDTO();
        }

        public bool DeleteHocKy(Guid id)
        {
            return StoreProcedureExecutor.ExecuteNonQueryStoredProcedure("[dbo].[usp_HocKyDelete]", new SqlParameter("@MaHocKy", id));
        }

        #region Private Methods
        private SqlParameter[] GetParameterArray(HocKyDTO hocKyDTO)
        {
            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@MaHocKy", hocKyDTO.MaHocKy);
            parameters[1] = new SqlParameter("@TenHocKy", hocKyDTO.TenHocKy);
            parameters[2] = new SqlParameter("@MaNamHoc", hocKyDTO.MaNamHoc);
            return parameters;
        }

        private SqlParameter[] GetParameterArray(NamHocDTO namHocDTO)
        {
            SqlParameter[] parameters = new SqlParameter[3];
            parameters[0] = new SqlParameter("@MaNamHoc", namHocDTO.MaNamHoc);
            parameters[1] = new SqlParameter("@Nam1", namHocDTO.Nam1);
            parameters[2] = new SqlParameter("@Nam2", namHocDTO.Nam2);
            return parameters;
        }
        #endregion


        public NamHocDTO GetNamHocById(Guid id)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_NamHocSelect]", new SqlParameter("@MaNamHoc", id));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToNamHocDTO();
        }

        public IEnumerable<NamHocDTO> GetAllNamHoc()
        {
            List<NamHocDTO> namHocList = new List<NamHocDTO>();
            SqlParameter parameter = new SqlParameter("@MaNamHoc", SqlDbType.UniqueIdentifier);
            parameter.Value = DBNull.Value;
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_NamHocSelect]", parameter);
            var giangvienTable = dbResult.Tables[0].Rows;
            foreach (DataRow dataRow in giangvienTable)
            {
                namHocList.Add(dataRow.ToNamHocDTO());
            }
            return namHocList.AsEnumerable();
        }

        public NamHocDTO InsertNamHoc(NamHocDTO namHocDTO)
        {
            var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_NamHocInsert]", GetParameterArray(namHocDTO));
            var dataRow = dbResult.Tables[0].Rows[0];
            return dataRow.ToNamHocDTO();
        }

        public bool DeleteNamHoc(Guid id)
        {
            return StoreProcedureExecutor.ExecuteNonQueryStoredProcedure("[dbo].[usp_NamHocDelete]", new SqlParameter("@MaNamHoc", id));
        }
    }
}
